import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';
import 'package:path_provider/path_provider.dart';
import '../models/note.dart';
import '../models/folder.dart';
import 'dart:convert';

class DatabaseService {
  static final DatabaseService _instance = DatabaseService._internal();
  factory DatabaseService() => _instance;
  DatabaseService._internal();

  static Database? _database;
  bool _isInitialized = false;

  Future<Database> get database async {
    if (!_isInitialized) {
      await _initDatabase();
    }
    return _database!;
  }

  Future<void> _initDatabase() async {
    if (_isInitialized) return;

    final externalDir = await getExternalStorageDirectory();
    if (externalDir == null) {
      throw Exception('无法访问外部存储');
    }

    final dbPath = join(externalDir.path, 'cnote.db');
    _database = await openDatabase(
      dbPath,
      version: 1,
      onCreate: _createTables,
    );

    _isInitialized = true;
  }

  Future<void> _createTables(Database db, int version) async {
    final List<Map<String, dynamic>> folderCheck = await db.rawQuery(
        "SELECT name FROM sqlite_master WHERE type='table' AND name='folders'");
    if (folderCheck.isEmpty) {
      await db.execute('''
        CREATE TABLE folders(
          id TEXT PRIMARY KEY,
          name TEXT NOT NULL,
          created_at TEXT NOT NULL,
          updated_at TEXT NOT NULL
        )
      ''');
    }

    final List<Map<String, dynamic>> notesCheck = await db.rawQuery(
        "SELECT name FROM sqlite_master WHERE type='table' AND name='notes'");
    if (notesCheck.isEmpty) {
      await db.execute('''
        CREATE TABLE notes(
          id TEXT PRIMARY KEY,
          title TEXT NOT NULL,
          content TEXT NOT NULL,
          created_at TEXT NOT NULL,
          updated_at TEXT NOT NULL,
          is_pinned INTEGER NOT NULL DEFAULT 0,
          folder_id TEXT,
          password TEXT,
          label TEXT,
          image_paths TEXT,
          audio_paths TEXT,
          FOREIGN KEY (folder_id) REFERENCES folders (id)
        )
      ''');
    }
    
    final List<Map<String, dynamic>> todosCheck = await db.rawQuery(
        "SELECT name FROM sqlite_master WHERE type='table' AND name='todos'");
    if (todosCheck.isEmpty) {
      await db.execute('''
        CREATE TABLE todos(
          id TEXT PRIMARY KEY,
          title TEXT NOT NULL,
          is_completed INTEGER NOT NULL DEFAULT 0,
          created_at TEXT NOT NULL,
          updated_at TEXT NOT NULL,
          reminder_time TEXT
        )
      ''');
    }
  }

  Future<List<Folder>> getAllFolders() async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query(
      'folders',
      orderBy: 'updated_at DESC',
    );

    return List.generate(maps.length, (i) {
      return Folder(
        id: maps[i]['id'],
        name: maps[i]['name'],
        createdAt: DateTime.parse(maps[i]['created_at']),
        updatedAt: DateTime.parse(maps[i]['updated_at']),
      );
    });
  }

  Future<void> insertFolder(Folder folder) async {
    final db = await database;
    await db.insert(
      'folders',
      {
        'id': folder.id,
        'name': folder.name,
        'created_at': folder.createdAt.toIso8601String(),
        'updated_at': folder.updatedAt.toIso8601String(),
      },
    );
  }

  Future<void> updateFolder(Folder folder) async {
    final db = await database;
    await db.update(
      'folders',
      {
        'name': folder.name,
        'updated_at': folder.updatedAt.toIso8601String(),
      },
      where: 'id = ?',
      whereArgs: [folder.id],
    );
  }

  Future<void> deleteFolder(String id) async {
    final db = await database;
    await db.delete(
      'notes',
      where: 'folder_id = ?',
      whereArgs: [id],
    );
    await db.delete(
      'folders',
      where: 'id = ?',
      whereArgs: [id],
    );
  }

  Future<List<Note>> getAllNotes() async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query(
      'notes',
      orderBy: 'is_pinned DESC, updated_at DESC',
    );

    return List.generate(maps.length, (i) => Note.fromMap(maps[i]));
  }

  Future<List<Note>> searchNotes(String query) async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query(
      'notes',
      where: 'title LIKE ? OR label LIKE ?',
      whereArgs: ['%$query%', '%$query%'],
      orderBy: 'is_pinned DESC, updated_at DESC',
    );
    return List.generate(maps.length, (i) => Note.fromMap(maps[i]));
  }

  Future<void> insertNote(Note note) async {
    final db = await database;
    await db.insert(
      'notes',
      {
        'id': note.id,
        'title': note.title,
        'content': note.content,
        'created_at': note.createdAt.toIso8601String(),
        'updated_at': note.updatedAt.toIso8601String(),
        'is_pinned': note.isPinned ? 1 : 0,
        'folder_id': note.folderId,
        'password': note.password,
        'label': note.label,
        'image_paths': json.encode(note.imagePaths),
        'audio_paths': json.encode(note.audioPaths),
      },
    );
  }

  Future<void> updateNote(Note note) async {
    final db = await database;
    await db.update(
      'notes',
      {
        'title': note.title,
        'content': note.content,
        'updated_at': note.updatedAt.toIso8601String(),
        'is_pinned': note.isPinned ? 1 : 0,
        'folder_id': note.folderId,
        'password': note.password,
        'label': note.label,
        'image_paths': json.encode(note.imagePaths),
        'audio_paths': json.encode(note.audioPaths),
      },
      where: 'id = ?',
      whereArgs: [note.id],
    );
  }
  
  Future<void> deleteNote(String id) async {
    final db = await database;
    await db.delete(
      'notes',
      where: 'id = ?',
      whereArgs: [id],
    );
  }
  
  Future<List<Map<String, dynamic>>> getAllTodos() async {
    final db = await database;
    return await db.query(
      'todos',
      orderBy: 'is_completed ASC, updated_at DESC', 
    );
  }
  
  Future<void> insertTodo(String id, String title, {DateTime? reminderTime}) async {
    final db = await database;
    
    await db.insert(
      'todos',
      {
        'id': id,
        'title': title,
        'is_completed': 0,
        'created_at': DateTime.now().toIso8601String(),
        'updated_at': DateTime.now().toIso8601String(),
        'reminder_time': reminderTime?.toIso8601String(),
      },
    );
  }
  
  Future<void> updateTodo(String id, String title, {DateTime? reminderTime}) async {
    final db = await database;
    await db.update(
      'todos',
      {
        'title': title,
        'updated_at': DateTime.now().toIso8601String(),
        'reminder_time': reminderTime?.toIso8601String(),
      },
      where: 'id =?',
      whereArgs: [id],
    );
  }

  Future<void> updateTodoStatus(String id, bool isCompleted) async {
    final db = await database;
    await db.update(
      'todos',
      {
        'is_completed': isCompleted ? 1 : 0,
        'updated_at': DateTime.now().toIso8601String(),
      },
      where: 'id = ?',
      whereArgs: [id],
    );
  }
  
  Future<void> deleteTodo(String id) async {
    final db = await database;
    await db.delete(
      'todos',
      where: 'id = ?',
      whereArgs: [id],
    );
  }
}